﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.ComponentModel;
using System.Text;
using System.Collections.ObjectModel;
using System.Windows;
using System.Windows.Input;
using SqlServerMaintenanceSolution.Commands;
using Microsoft.Win32;
using SqlServerMaintenanceSolution.Models;
using System.Windows.Data;
using System.Windows.Controls;
using SqlServerMaintenanceSolution.Models.Enums;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using AutoMapper;
using AutoMapper.Mappers;
using AutoMapper.Data;
using SqlServerMaintenanceSolution.Models;


namespace SqlServerMaintenanceSolution.ViewModels
{
    public class IndexFragmentViewModel : ViewModelBase, INotifyPropertyChanged
    {
        private DelegateCommand exitCommand;

        private SqlServerAuthentication _sqlServerAuthentication;
        private SqlServerInstance _sqlServerInstance;
        private DataBase _selectedDatabase;
        private ObservableCollection<DataBase> _databases = new ObservableCollection<DataBase>();
        private DatabaseIndexDetail _selectedIndex = new DatabaseIndexDetail();
        public DatabaseIndexDetail SelectedIndex  
        {
            get { return _selectedIndex; }
            set
            {
                _selectedIndex = value;
                OnPropertyChanged("SelectedIndex");
            }
        }

        private ObservableCollection<DatabaseIndexDetail> _indexdetails = new ObservableCollection<DatabaseIndexDetail>();
        public ObservableCollection<DatabaseIndexDetail> DatabaseIndexDetails
        {
            get { return _indexdetails; }
        }
        private DatabaseFileDetail _selectedDatabaseFileDetail;
        public DatabaseFileDetail SelectedDatabaseFileDetail
        {
            get { return _selectedDatabaseFileDetail; }
            set
            {
                _selectedDatabaseFileDetail = value;
                OnPropertyChanged("DatabaseFileDetail");
            }
        }
        public DataBase SelectedDataBase 
        { 
            get { return  _selectedDatabase; } 
            set {
                if ((_selectedDatabase != value) && (value != null))
                {
                    _selectedDatabase = value;
                    this._indexdetails = getListIndexDetails(_selectedDatabase);
                    OnPropertyChanged("DatabaseIndexDetails");
                    OnPropertyChanged("SelectedDataBase");
                }
            } 
        }
        public  ObservableCollection<DataBase> Databases
        {
            get { return _databases; }
        }
        
        public SqlServerAuthentication SqlServerAuthentication
            {
                get { return _sqlServerAuthentication; }
            set
            {
                if (_sqlServerAuthentication == value) return;
                _sqlServerAuthentication = value;

                OnPropertyChanged("SqlServerAuthentication");
            }
        }

        public SqlServerInstance SqlServerInstance
        {
            get { return _sqlServerInstance; }
            set
            {
                if (_sqlServerInstance == value) return;
                _sqlServerInstance = value;

                OnPropertyChanged("SqlServerInstance");
            }
        }

        public ICommand ExitCommand
        {
            get
            {
                if (exitCommand == null)
                {
                    exitCommand = new DelegateCommand(Exit);
                }
                return exitCommand;
            }
        }

        private void Exit()
        {
            Application.Current.Shutdown();
        }

        public IndexFragmentViewModel()
        {
            Mapper.Initialize(cfg =>
            {
                MapperRegistry.Mappers.Add(new DataReaderMapper());
                // Other config
            });
        }

        public IndexFragmentViewModel(SqlServerInstance sqlServerInstance, SqlServerAuthentication sqlServerAuthentication)
            : base()
        {
            this.SqlServerAuthentication = sqlServerAuthentication;
            this.SqlServerInstance = sqlServerInstance;    
            
        }

        private ObservableCollection<DataBase> getListDatabases(Models.SqlServerInstance sqlServerInstance, Models.SqlServerAuthentication sqlServerAuthentication)
        {
            ObservableCollection<DataBase> databases = new ObservableCollection<DataBase>();

            SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder();

            // if using default instance MSSQLSERVER, simply connect to that instance either by specifying server name DBServer or (local) or . (just a dot)
            if (sqlServerInstance.isDefaultInstance())
                connection.DataSource = "(local)";
            else
                connection.DataSource = sqlServerInstance.CompleteName;
            // enter credentials if you want
            //connection.UserID = //get username;
            // connection.Password = //get password;

            if (sqlServerAuthentication.IntegratedSecurity)
                connection.IntegratedSecurity = true;
            else
                {
                    connection.UserID = sqlServerAuthentication.UserID;
                    connection.Password = sqlServerAuthentication.Password;
                }

            String strConn = connection.ToString();
            string script = File.ReadAllText(@"..\..\SQL Scripts\spaceused by all databases.sql");
             
            //create connection
            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlCommand cmd;
            try
            {
                //open connection
                sqlConn.Open();
                cmd = new SqlCommand(script, sqlConn);
    
                List<DataBase> lst = this.ReadData<DataBase>(script, sqlConn, strConn);              
                databases = new ObservableCollection<DataBase>(lst);

                cmd.Dispose();
                //close connection
                sqlConn.Close();
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
            return databases;
        }

        private ObservableCollection<DatabaseIndexDetail> getListIndexDetails(DataBase selectedDatabase)
        {
            ObservableCollection<DatabaseIndexDetail> databaseIndexDetails = new ObservableCollection<DatabaseIndexDetail>();

            SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder();

            // if using default instance MSSQLSERVER, simply connect to that instance either by specifying server name DBServer or (local) or . (just a dot)
            if (this.SqlServerInstance.isDefaultInstance())
                connection.DataSource = "(local)";
            else
                connection.DataSource = this.SqlServerInstance.CompleteName;
            // enter credentials if you want
            //connection.UserID = //get username;
            // connection.Password = //get password;

            if (this.SqlServerAuthentication.IntegratedSecurity)
                connection.IntegratedSecurity = true;
            else
            {
                connection.UserID = this.SqlServerAuthentication.UserID;
                connection.Password = this.SqlServerAuthentication.Password;
            }

            String strConn = connection.ToString();
            string script = File.ReadAllText(@"..\..\SQL Scripts\index fragmentation.sql");
            script = string.Format(script, selectedDatabase.DatabaseName);

            //create connection
            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlCommand cmd;
            try
            {
                //open connection
                sqlConn.Open();
                cmd = new SqlCommand(script, sqlConn);

                List<DatabaseIndexDetail> lst = this.ReadData<DatabaseIndexDetail>(script, sqlConn, strConn);
                databaseIndexDetails = new ObservableCollection<DatabaseIndexDetail>(lst);

                cmd.Dispose();
                //close connection
                sqlConn.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return databaseIndexDetails;
        }

        private ObservableCollection<BaseModel> ConvertListToObservableCollection(List<BaseModel> list)
        {
            ObservableCollection<BaseModel> oc = new ObservableCollection<BaseModel>();
            foreach (var item in list)
                oc.Add(item);

            return oc;
        }

        public List<T> ReadData<T>(string queryString, SqlConnection sqlConn, string constr)
        {
                    
            var type = typeof(DataReaderMapper);
            using (var connection = new SqlConnection(constr))
            using (var command = new SqlCommand(queryString, connection))
            {
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                        return Mapper.DynamicMap<IDataReader, List<T>>(reader);
                    //return AutoMapper.Mapper.DynamicMap<IDataReader, List<T>>(dt.CreateDataReader());           
                }
            }

            return null;
        }

        

        private void OnPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
        public event PropertyChangedEventHandler PropertyChanged;
 

        #region Commands
        public ICommand LoadDBsClickCommand
        {
            get { return new DelegateCommand<object>(LoadDBsClick, CanClickLoadDBs); }
        }

        private void LoadDBsClick(object context)
        {
            this._databases = getListDatabases(this.SqlServerInstance, this.SqlServerAuthentication);
            if (this.SelectedDataBase != null)
                this._indexdetails = getListIndexDetails(this.SelectedDataBase);
            OnPropertyChanged("Databases");
        }

        private bool CanClickLoadDBs(object context)
        {
            return true;
        }

        public ICommand RebuildClickCommand
        {
            get { return new DelegateCommand<object>(RebuildClick, CanClickRebuild); }
        }

        private void RebuildClick(object context)
        {

            SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder();

            // if using default instance MSSQLSERVER, simply connect to that instance either by specifying server name DBServer or (local) or . (just a dot)
            if (this.SqlServerInstance.isDefaultInstance())
                connection.DataSource = "(local)";
            else
                connection.DataSource = this.SqlServerInstance.CompleteName;
            // enter credentials if you want
            //connection.UserID = //get username;
            // connection.Password = //get password;

            if (this.SqlServerAuthentication.IntegratedSecurity)
                connection.IntegratedSecurity = true;
            else
            {
                connection.UserID = this.SqlServerAuthentication.UserID;
                connection.Password = this.SqlServerAuthentication.Password;
            }

            String strConn = connection.ToString();
            string script = File.ReadAllText(@"..\..\SQL Scripts\index rebuild.sql");
            script = string.Format(script, this.SelectedDataBase.DatabaseName, this.SelectedIndex.Index, this.SelectedIndex.Schema, this.SelectedIndex.Table);

            //create connection
            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlCommand cmd;
            try
            {
                //open connection
                sqlConn.Open();
                cmd = new SqlCommand(script, sqlConn);

                cmd.ExecuteNonQuery();

                cmd.Dispose();
                //close connection
                sqlConn.Close();
               

                //
                this._indexdetails = this.getListIndexDetails(this.SelectedDataBase);
                OnPropertyChanged("DatabaseIndexDetails");

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

      
          
        }

        private bool CanClickRebuild(object context)
        {
            return true;
        }

        #endregion
    }
}
